﻿using System;
using System.Data;
using System.Data.SqlClient;
using Wedn.Core.Entity;
using Wedn.Core.Utility;

namespace Wedn.Utility
{
    /// <summary>
    /// SQL Server数据库访问助手类
    /// 本类为抽象类 不可以被实例化 需要使用时直接调用即可
    /// Copyright © 2013 Wedn.Net
    /// </summary>
    public abstract class SqlHelper
    {
        /// <summary>
        /// 数据库连接字符串
        /// </summary>
        private readonly static string connStr = Wedn.Core.Utility.Config.SqlConnectionString;
        //private readonly static string connStr = System.Configuration.ConfigurationManager.ConnectionStrings["sqlConnStr"].ConnectionString;

        #region 公共方法
        /// <summary>
        /// 获取指定表中指定字段的最大值, 确保字段为INT类型
        /// </summary>
        /// <param name="fieldName">字段名</param>
        /// <param name="tableName">表名</param>
        /// <returns>最大值</returns>
        public static int SelectMaxId(string fieldName, string tableName)
        {
            string sql = string.Format("select max([{0}]) from [{1}];", fieldName, tableName);
            object res = ExecuteScalar(sql);
            if (res == null)
                return 0;
            return int.Parse(res.ToString());
        }

        /// <summary>
        /// 表是否存在
        /// </summary>
        /// <param name="TableName"></param>
        /// <returns></returns>
        public static bool ExistsTable(string TableName)
        {
            string sql = "select count(1) from sysobjects where id = object_id(N'[" + TableName + "]') and OBJECTPROPERTY(id, N'IsUserTable') = 1";
            //string strsql = "SELECT count(*) FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[" + TableName + "]') AND type in (N'U')";
            object res = ExecuteScalar(sql);
            int cmdresult;
            if ((Object.Equals(res, null)) || (Object.Equals(res, System.DBNull.Value)))
            {
                cmdresult = 0;
            }
            else
            {
                cmdresult = int.Parse(res.ToString());
            }
            if (cmdresult == 0)
            {
                return false;
            }
            else
            {
                return true;
            }
        }

        /// <summary>
        /// 判断是否存在某张表的某个字段
        /// </summary>
        /// <param name="tableName">表名称</param>
        /// <param name="columnName">列名称</param>
        /// <returns>是否存在</returns>
        public static bool ExistsColumn(string tableName, string columnName)
        {
            string sql = "select count(1) from syscolumns where [id]=object_id('N[" + tableName + "]') and [name]='" + columnName + "'";
            object res = ExecuteScalar(sql);
            if (res == null)
                return false;
            return Convert.ToInt32(res) > 0;
        }

        /// <summary>
        /// 判断某张表的某个字段中是否存在某个值
        /// </summary>
        /// <param name="tableName">表名称</param>
        /// <param name="columnName">列名称</param>
        /// <param name="value">要判断的值</param>
        /// <returns>是否存在</returns>
        public static bool ColumnExistsValue(string tableName, string columnName, string value)
        {
            string sql = "SELECT count(1) FROM [" + tableName + "] WHERE [" + columnName + "]=@Value;";
            object res = ExecuteScalar(sql, new SqlParameter("@Value", value));
            if (res == null)
                return false;
            return Convert.ToInt32(res) > 0;
        }

        /// <summary>
        /// 生成分页查询数据库语句, 返回生成的T-SQL语句
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="colList">列集合, 多个列用英文逗号分割(colum1,colum2...)</param>
        /// <param name="pageSize">显示页面大小(即显示条数)</param>
        /// <param name="pageIndex">页码(即第几页)(传入-1则表示忽略分页取出全部)</param>
        /// <param name="orderField">排序字段(即根据那个字段排序)(忽略则传入null)</param>
        /// <param name="order">排序方式(0:降序(desc)|1:升序(asc))(忽略则传入-1)</param>
        /// <param name="where">条件语句(忽略则传入null)</param>
        /// <returns>生成的T-SQL语句</returns>
        public static string MakePagedSql(string tableName, string colList, int pageSize, int pageIndex, string orderField, int order, string where)
        {
            string temp, sql;
            if (pageIndex == 1)
            {
                temp = "select top {0} {1} from [{2}] {3} {4} {5}";
                sql = string.Format(temp,
                    pageSize,
                    colList,
                    tableName,
                    string.IsNullOrEmpty(where) ? string.Empty : "where " + where,
                    string.IsNullOrEmpty(orderField) ? string.Empty : "order by " + orderField,
                    order != -1 && !string.IsNullOrEmpty(orderField) ? "desc" : string.Empty);
            }
            else if (pageIndex == -1)
            {
                temp = "select {0} from [{1}] {2} {3} {4}";
                sql = string.Format(temp,
                    colList,
                    tableName,
                    string.IsNullOrEmpty(where) ? string.Empty : "where " + where,
                    string.IsNullOrEmpty(orderField) ? string.Empty : "order by " + orderField,
                    order != -1 && !string.IsNullOrEmpty(orderField) ? "desc" : string.Empty);
            }
            else
            {
                //这样做效率好
                temp = @"select {0} from
                                    (
                                         select ROW_NUMBER() over(order by [{1}] {2}) as num, {0} from [{3}] {4}
                                    )
                                    as tbl
                                    where
                                        tbl.num between ({5}-1)*{6} + 1 and {5}*{6};";
                sql = string.Format(temp,
                    colList,
                    orderField,
                    order == 0 ? "desc" : "asc",
                    tableName,
                    !string.IsNullOrEmpty(where) ? "where " + where : string.Empty,
                    pageIndex,
                    pageSize);
                #region MyRegion
                //                temp = @"select top {0}
                //	                                  {1} 
                //                                  from [{2}] 
                //                                  where
                //                                      [{3}] not in (select top (({4}-1)*{0}) [{3}] from [{2}] order by [{3}] {5}) 
                //	                                  {6}
                //                                  order by [{3}] {5};";
                //                sql = string.Format(temp,
                //                    pageSize,
                //                    colList,
                //                    tableName,
                //                    orderField,
                //                    pageIndex,
                //                    order == 0 ? "desc" : "asc",
                //                    !string.IsNullOrEmpty(where) ? "and " + where : string.Empty); 
                #endregion
            }
            return sql;
            #region MyRegion
            //string temp = string.Empty;
            //string sql = string.Empty;
            //if (string.IsNullOrEmpty(condition))
            //{
            //    condition = " 1=1 ";
            //}
            ////第一页
            //if (pageIndex == 1)
            //{
            //    temp = "select top {0} {1} from [{2}] where {3} order by [{4}] {5}";
            //    sql = string.Format(temp, pageSize, colList, tableName, condition, orderField, order == 0 ? "desc" : "asc");
            //}
            //else
            //{
            //    //降序
            //    if (order == 0)
            //    {
            //        temp = "select top {0} {1} from [{2}] where {5} and {3} <(select min(pk) from ( select top {4} {3} as pk from {2} where {5} order by [{3}] desc) t) order by [{3}] desc";
            //        sql = string.Format(temp, pageSize, colList, tableName, orderField, pageSize * (pageIndex - 1), condition);
            //    }
            //    //升序
            //    if (order == 1)
            //    {
            //        temp = "select top {0} {1} from [{2}] where {5} and {3} >(select max(pk) from ( select top {4} {3} as pk from {2} where {5} order by [{3}] asc) t) order by [{3}] asc";
            //        sql = string.Format(temp, pageSize, colList, tableName, orderField, pageSize * (pageIndex - 1), condition);
            //    }
            //}
            //return sql; 
            #endregion
        }
        #endregion

        #region ExecuteNonQuery
        /// <summary>
        /// 执行一个非查询的T-SQL语句，返回受影响行数，如果执行的是非增、删、改操作，返回-1
        /// </summary>
        /// <param name="cmdText">要执行的T-SQL语句</param>
        /// <param name="type">命令类型</param>
        /// <param name="parameters">参数列表</param>
        /// <exception cref="链接数据库异常"></exception>
        /// <returns>受影响的行数</returns>
        public static int ExecuteNonQuery(string cmdText, CommandType type, params SqlParameter[] parameters)
        {
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                cmdText = cmdText.Replace("&#WednTablePrefix%&_", Config.TablePrefix);
                using (SqlCommand cmd = new SqlCommand(cmdText, conn))
                {
                    if (parameters != null)
                    {
                        cmd.Parameters.Clear();
                        cmd.Parameters.AddRange(parameters);
                    }
                    cmd.CommandType = type;
                    try
                    {
                        conn.Open();
                        int res = cmd.ExecuteNonQuery();
                        QueryInfo.QueryCount += 1;
                        cmd.Parameters.Clear();
                        return res;
                    }
                    catch (System.Data.SqlClient.SqlException e)
                    {
                        conn.Close();
                        throw e;
                    }
                }
            }
        }
        /// <summary>
        /// 执行一个非查询的T-SQL语句，返回受影响行数，如果执行的是非增、删、改操作，返回-1
        /// </summary>
        /// <param name="cmdText">要执行的T-SQL语句</param>
        /// <param name="parameters">参数列表</param>
        /// <exception cref="链接数据库异常"></exception>
        /// <returns>受影响的行数</returns>
        public static int ExecuteNonQuery(string cmdText, params SqlParameter[] parameters)
        {
            return ExecuteNonQuery(cmdText, CommandType.Text, parameters);
        }
        #endregion

        #region ExecuteScalar
        /// <summary>
        /// 执行一个查询的T-SQL语句，返回第一行第一列的结果
        /// </summary>
        /// <param name="cmdText">要执行的T-SQL语句</param>
        /// <param name="type">命令类型</param>
        /// <param name="parameters">参数列表</param>
        /// <exception cref="链接数据库异常"></exception>
        /// <returns>返回第一行第一列的数据</returns>
        public static object ExecuteScalar(string cmdText, CommandType type, params SqlParameter[] parameters)
        {
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                cmdText = cmdText.Replace("&#WednTablePrefix%&_", Config.TablePrefix);
                using (SqlCommand cmd = new SqlCommand(cmdText, conn))
                {
                    if (parameters != null)
                    {
                        cmd.Parameters.Clear();
                        cmd.Parameters.AddRange(parameters);
                    }
                    cmd.CommandType = type;
                    try
                    {
                        conn.Open();
                        object res = cmd.ExecuteScalar();
                        QueryInfo.QueryCount += 1;
                        cmd.Parameters.Clear();
                        return res;
                    }
                    catch (System.Data.SqlClient.SqlException e)
                    {
                        conn.Close();
                        throw e;
                    }
                }
            }
        }

        /// <summary>
        /// 执行一个查询的T-SQL语句，返回第一行第一列的结果
        /// </summary>
        /// <param name="cmdText">要执行的T-SQL语句</param>
        /// <param name="parameters">参数列表</param>
        /// <exception cref="链接数据库异常"></exception>
        /// <returns>返回第一行第一列的数据</returns>
        public static object ExecuteScalar(string cmdText, params SqlParameter[] parameters)
        {
            return ExecuteScalar(cmdText, CommandType.Text, parameters);
        }
        #endregion

        #region ExecuteReader
        /// <summary>
        /// 利用委托 执行一个大数据查询的T-SQL语句
        /// </summary>
        /// <param name="cmdText">要执行的T-SQL语句</param>
        /// <param name="type">命令类型</param>
        /// <param name="action">传入执行的委托对象</param>
        /// <param name="parameters">参数列表</param>
        /// <exception cref="链接数据库异常"></exception>
        public static void ExecuteReader(string cmdText, CommandType type, Action<SqlDataReader> action, params SqlParameter[] parameters)
        {
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                cmdText = cmdText.Replace("&#WednTablePrefix%&_", Config.TablePrefix);
                using (SqlCommand cmd = new SqlCommand(cmdText, conn))
                {
                    if (parameters != null)
                    {
                        cmd.Parameters.Clear();
                        cmd.Parameters.AddRange(parameters);
                    }
                    cmd.CommandType = type;
                    try
                    {
                        conn.Open();
                        using (SqlDataReader reader = cmd.ExecuteReader())
                        {
                            action(reader);
                        }
                        QueryInfo.QueryCount += 1;
                        cmd.Parameters.Clear();
                    }
                    catch (System.Data.SqlClient.SqlException e)
                    {
                        conn.Close();
                        throw e;
                    }
                }
            }
        }

        /// <summary>
        /// 利用委托 执行一个大数据查询的T-SQL语句
        /// </summary>
        /// <param name="cmdText">要执行的T-SQL语句</param>
        /// <param name="action">传入执行的委托对象</param>
        /// <param name="parameters">参数列表</param>
        /// <exception cref="链接数据库异常"></exception>
        public static void ExecuteReader(string cmdText, Action<SqlDataReader> action, params SqlParameter[] parameters)
        {
            ExecuteReader(cmdText, CommandType.Text, parameters);
        }

        /// <summary>
        /// 执行一个查询的T-SQL语句, 返回一个SqlDataReader对象, 如果出现SQL语句执行错误, 将会关闭连接通道抛出异常
        ///  ( 注意：调用该方法后，一定要对SqlDataReader进行Close )
        /// </summary>
        /// <param name="cmdText">要执行的T-SQL语句</param>
        /// <param name="type">命令类型</param>
        /// <param name="parameters">参数列表</param>
        /// <exception cref="链接数据库异常"></exception>
        /// <returns>SqlDataReader对象</returns>
        public static SqlDataReader ExecuteReader(string cmdText, CommandType type, params SqlParameter[] parameters)
        {
            SqlConnection conn = new SqlConnection(connStr);
            cmdText = cmdText.Replace("&#WednTablePrefix%&_", Config.TablePrefix);
            using (SqlCommand cmd = new SqlCommand(cmdText, conn))
            {
                if (parameters != null)
                {
                    cmd.Parameters.Clear();
                    cmd.Parameters.AddRange(parameters);
                }
                cmd.CommandType = type;
                conn.Open();
                try
                {
                    SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    QueryInfo.QueryCount += 1;
                    cmd.Parameters.Clear();
                    return reader;
                }
                catch (System.Data.SqlClient.SqlException ex)
                {
                    //出现异常关闭连接并且释放
                    conn.Close();
                    throw ex;
                }
            }
        }

        /// <summary>
        /// 执行一个查询的T-SQL语句, 返回一个SqlDataReader对象, 如果出现SQL语句执行错误, 将会关闭连接通道抛出异常
        ///  ( 注意：调用该方法后，一定要对SqlDataReader进行Close )
        /// </summary>
        /// <param name="cmdText">要执行的T-SQL语句</param>
        /// <param name="parameters">参数列表</param>
        /// <exception cref="链接数据库异常"></exception>
        /// <returns>SqlDataReader对象</returns>
        public static SqlDataReader ExecuteReader(string cmdText, params SqlParameter[] parameters)
        {
            return ExecuteReader(cmdText, CommandType.Text, parameters);
        }
        #endregion

        #region ExecuteDataSet
        /// <summary>
        /// 执行一个查询的T-SQL语句, 返回一个离线数据集DataSet
        /// </summary>
        /// <param name="cmdText">要执行的T-SQL语句</param>
        /// <param name="type">命令类型</param>
        /// <param name="parameters">参数列表</param>
        /// <returns>离线数据集DataSet</returns>
        public static DataSet ExecuteDataSet(string cmdText, CommandType type, params SqlParameter[] parameters)
        {
            cmdText = cmdText.Replace("&#WednTablePrefix%&_", Config.TablePrefix);
            using (SqlDataAdapter adapter = new SqlDataAdapter(cmdText, connStr))
            {
                using (DataSet ds = new DataSet())
                {
                    if (parameters != null)
                    {
                        adapter.SelectCommand.Parameters.Clear();
                        adapter.SelectCommand.Parameters.AddRange(parameters);
                    }
                    adapter.SelectCommand.CommandType = type;
                    adapter.Fill(ds);
                    QueryInfo.QueryCount += 1;
                    adapter.SelectCommand.Parameters.Clear();
                    return ds;
                }
            }
        }

        /// <summary>
        /// 执行一个查询的T-SQL语句, 返回一个离线数据集DataSet
        /// </summary>
        /// <param name="cmdText">要执行的T-SQL语句</param>
        /// <param name="parameters">参数列表</param>
        /// <returns>离线数据集DataSet</returns>
        public static DataSet ExecuteDataSet(string cmdText, params SqlParameter[] parameters)
        {
            return ExecuteDataSet(cmdText, CommandType.Text, parameters);
        }
        #endregion

        #region ExecuteDataTable
        /// <summary>
        /// 执行一个数据表查询的T-SQL语句, 返回一个DataTable
        /// </summary>
        /// <param name="cmdText">要执行的T-SQL语句</param>
        /// <param name="type">命令类型</param>
        /// <param name="parameters">参数列表</param>
        /// <returns>查询到的数据表</returns>
        public static DataTable ExecuteDataTable(string cmdText, CommandType type, params SqlParameter[] parameters)
        {
            return ExecuteDataSet(cmdText, type, parameters).Tables[0];
        }
        /// <summary>
        /// 执行一个数据表查询的T-SQL语句, 返回一个DataTable
        /// </summary>
        /// <param name="cmdText">要执行的T-SQL语句</param>
        /// <param name="parameters">参数列表</param>
        /// <returns>查询到的数据表</returns>
        public static DataTable ExecuteDataTable(string cmdText, params SqlParameter[] parameters)
        {
            return ExecuteDataTable(cmdText, CommandType.Text, parameters);
        }
        #endregion
    }
}
